﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Runtime.Serialization.Formatters.Binary;

namespace LX.Cache.Providers
{
    public class SqlServerCacheProvider : ICacheProvider,IDisposable
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnectionString = "";

        /// <summary>
        /// 缓存表
        /// </summary>
        public string CacheTable = "Cache";

        public string KeyColumn = "CacheKey";

        public string ValueColumn = "Value";

        public string ExpiresColumn = "Expires";

        public SqlServerCacheProvider(){}

        public SqlServerCacheProvider(string str)
        {
            ConnectionString = str;
        }

        private SqlConnection _conn;
        private SqlConnection conn
        {
            get
            {
                if (_conn == null)
                {
                    _conn = new SqlConnection(ConnectionString);
                    _conn.Open();
                }
                return _conn;
            }
        }

        public void Set(string key, object value)
        {
            byte[] dbvalue = Serialize(value);
            if (dbvalue == null) return;

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select id from " + CacheTable + "  where " + KeyColumn + "=@key";
            cmd.Parameters.Add(new SqlParameter("key", key));
            object result = cmd.ExecuteScalar();
            if (result == null)
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "Insert Into " + CacheTable + "(" + KeyColumn + "," + ValueColumn +
                                  ")values(@key,@value)";
                cmd.Parameters.Add(new SqlParameter("key", key));
                cmd.Parameters.Add(new SqlParameter("value", dbvalue));
                cmd.ExecuteNonQuery();
            }
            else
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "update " + CacheTable + " set " + ValueColumn +
                                  "=@value where id="+result.ToString();
                cmd.Parameters.Add(new SqlParameter("value", dbvalue));
                cmd.ExecuteNonQuery();
            }
        }

        public void Set(string key, object value, int expiresSecond)
        {
            byte[] dbvalue = Serialize(value);
            if (dbvalue == null) return;

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select id from " + CacheTable + "  where " + KeyColumn + "=@key";
            cmd.Parameters.Add(new SqlParameter("key", key));
            object result = cmd.ExecuteScalar();
            if (result == null)
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "Insert Into " + CacheTable + "(" + KeyColumn + "," + ValueColumn + "," + ExpiresColumn + ")values(@key,@value,@expires)";
                cmd.Parameters.Add(new SqlParameter("key", key));
                cmd.Parameters.Add(new SqlParameter("value", dbvalue));
                cmd.Parameters.Add(new SqlParameter("expires", DateTime.Now.AddSeconds(expiresSecond)));
                cmd.ExecuteNonQuery();
            }
            else
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = "update " + CacheTable + " set " + ValueColumn +
                                  "=@value," + ExpiresColumn + "=@expires where id=" + result.ToString();
                cmd.Parameters.Add(new SqlParameter("value", dbvalue));
                cmd.Parameters.Add(new SqlParameter("expires", DateTime.Now.AddSeconds(expiresSecond)));
                cmd.ExecuteNonQuery();
            }
        }

        public object GetData(string key)
        {
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "Select top 1 " + ValueColumn + " from " + CacheTable + " where " + KeyColumn + "=@key and (" + ExpiresColumn + " is null or " + ExpiresColumn + ">getdate() )";
            cmd.Parameters.Add(new SqlParameter("key", key));
            object result= cmd.ExecuteScalar();
            if (result == null) return result;
            byte[] data = result as byte[];
            return DeSerialize(data);
        }

        public void Remove(string key)
        {
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "Delete " + CacheTable + " where " + KeyColumn + "=@key";
            cmd.Parameters.Add(new SqlParameter("key", key));
            cmd.ExecuteNonQuery();
        }

        public void RemoveAll()
        {
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "Delete " + CacheTable ;
            cmd.ExecuteNonQuery();
        }

        public void Dispose()
        {
            if(conn!=null&&conn.State== System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }

        private byte[] Serialize(object value)
        {
            try
            {
                BinaryFormatter binaryFormatter = new BinaryFormatter();
                MemoryStream ms = new MemoryStream();
                byte[] b;
                binaryFormatter.Serialize(ms, value);
                ms.Position = 0;
                b = new Byte[ms.Length];
                ms.Read(b, 0, b.Length);
                ms.Close();
                return b;
            }catch
            {
                return null;
            }
        } 

        private object DeSerialize(byte[] data)
        {
            var binaryFormatter = new BinaryFormatter();
            var ms = new MemoryStream();
            try
            {
                ms.Write(data, 0, data.Length);
                ms.Position = 0;
                object obj = binaryFormatter.Deserialize(ms);
                return obj;
            }
            catch
            {
                return null;
            }
            finally
            {
                ms.Close();
            }         
        } 
    }
}
